Mega Code Archive - Oracle PLSQL Tutorial

Collections
1) A procedure that uses an implicit cursor loop to load an array with employee number and name
2) A procedure that uses EXISTS and LAST
3) Accessing an entire Index-by table
4) Accessing an Index-by table
5) Accessing an undefined row of an Index-by table
6) Accessing Nested Table elements
7) Accessing VARRAY elements
8) An anonymous PLSQL procedure to demonstrate the elementary use of PLSQL tables
9) An anonymous PLSQL procedure to demonstrate the use of PLSQL records
10) An example of COUNT method
11) An example of declaring an Index-by table
12) An example of the DELETE method
13) An example of the FIRST, LAST and NEXT methods
14) ANSI Support for Nested Tables
15) Assign value to PLSQL table
16) Assign value to VARRAY
17) Assigning rows of an Index-By table by means of a LOOP
18) Associative arrays
19) Associative arrays (index-by tables)
20) Associative Arrays demo
21) Bulk DML with table of records
22) CARDINALITY Operator
23) CARDINALITY operator lets count the elements in a collection
24) CARDINALITY table collection
25) Cardinality
26) Change PLSQL table element by index
27) Clear the salaries table by assigning the empty version to it
28) COLLECT Operator
29) Collection Methods
30) Collections stores sets of elements
31) Column_value is a built-in functionpseudo-variable that is held over from the DBMS_SQL package
32) Compare table of varchar2
33) Compare two tables of integers
34) Constructor of table collection
35) COUNT
36) CREATE TABLE with a VARRAY
37) Creating a Nested Table Type
38) Creating a Varray Type
39) Decreasing the Size of an Array
40) Define function to get the first value in varray
41) Defining a VARRAY
42) Defining an Index-by table of records
43) Defining our type to be a VARRAY with 10 elements, where each element is a varying character string of up to 15 characters
44) DELETE on varray column
45) Delete one record in the nested table
46) Deleting an Index-by table using an empty Index-by table
47) Deleting internal elements from a collection
48) Demonstrate Associative Arrays
49) Display the structure of a table with a valarray column
50) EXISTS and LAST
51) EXTEND
52) Extend and count attributes of varray
53) Extend Table collection
54) Extracting individual members of a VARRAY may be accomplished using two other functions
55) Fetch bulk collect to table of rowtype
56) FIRST and LAST Table Attributes
57) For index between first and last
58) Forall in value of table of type
59) Getting Information on Nested Tables
60) Getting Information on Varrays
61) If aliases are used, they must be used consistently
62) IN and NOT IN Operators
63) Index by string
64) Indexing Associative Arrays
65) Initialize table collection of varchar2
66) Initialize table collection with null value
67) Initializing a Nested Table
68) Initializing a VARRAY
69) Insert data in PLSQL table to a real table
70) Insert on whole VARRAYS
71) Insert value into table with nested type colunm
72) Inside the loop, you are accessing array elements by their subscripts
73) IS A SET Operator
74) IS A SET operator checks whether a variable is a VARRAY or NESTED TABLE collection variable
75) IS A SET, table collection
76) Is a table collection a null value
77) Is a varray null value
78) IS EMPTY Operator
79) IS EMPTY operator checks whether a VARRAY or NESTED TABLE collection variable is empty
80) IS NULL operator checks whether a variable value is null
81) LAST and COUNT give the same result for VARRAYs
82) LIMIT
83) List of Collection methods
84) Loading a Table with a VARRAY in It
85) Loading and accessing an array of varchar2
86) Loop through by using the built-in NEXT, PRIOR
87) Loop through table of number by index
88) Manipulating the VARRAY with The TABLE Function
89) Manipulating the VARRAY with The VARRAY Self-join
90) MEMBER OF is a logical comparison operator
91) MEMBER OF Operator
92) MEMBER OF operator finds if the left operand is a member of the collection used as the right operand
93) MEMBER OF table collection
94) Modifying Nested Table Elements
95) Modifying Varray Elements
96) MULTISET EXCEPT operator finds the elements remaining from the first set after removing any matching elements from the second
97) MULTISET EXCEPT table collection
98) MULTISET INTERSECT operator finds the intersection or matching values between two sets
99) MULTISET INTERSECT table collection
100) MULTISET Operator
101) MULTISET UNION DISTINCT table collection
102) MULTISET UNION operator performs a UNION ALL operation on two collections
103) MULTISET UNION table collection
104) Multilevel Collection Types
105) Multilevel Collections
106) Nested Table OF varchar2
107) Nested Table with Table row elements inside
108) Nested Tables
109) Nested type column
110) NEXT and PRIOR Table Attributes
111) NOT IN example (table collection)
112) NULL and Empty Nested Tables and NULL elements
113) NULL VARRAYS and NULL Elements
114) Number_list EXTEND(2)
115) Number_list EXTEND(3,4)
116) One way to make the members behave like an array is first to include the row number in the result set like this
117) ORA-06531
118) ORA-06533
119) Pass NULL value to varray
120) Place some values into the salaries table
121) PLSQL Table
122) PLSQL table of cursor
123) Populating a Nested Table with Elements
124) Populating a Varray with Elements
125) POWERMULTISET Operator
126) POWERMULTISET_BY_CARDINALITY Operator
127) Query table with VARRAY type column by column name
128) Query VARRAY column
129) Reference elements in table collection of varchar2 by index
130) Select data into PLSQL table of cursor
131) SELECT into with varray column
132) Select user-defined type into table collection of user-defined types
133) Selecting Nested Table Elements
134) Selecting Varray Elements
135) Set empty varray value
136) SET Operator
137) SET operator removes any duplicates from the set and returns a new set with unique values
138) Set the depth to 2 and describe a nested table
139) SET(table collection)
140) Stored VARRAYS
141) SUBMULTISET
142) SUBMULTISET Operator
143) SUBMULTISET operator checks whether a VARRAY or NESTED TABLE collection is a subset of a mirrored datatype
144) SUBMULTISET table collection
145) Table collection of records
146) Table collection variable assignment
147) TABLE Exist
148) Table of numbers
149) Table of rowtype indexed by integer
150) Table of type
151) Table of user-defined types
152) Table with subquery
153) The CAST function converts an object type (such as a VARRAY) into a common type that can be queried Oracle 10g automatically c
154) The COUNT Function
155) The COUNT Table Attribute
156) The DELETE Table Attribute
157) The EXISTS Table Attribute
158) The subscript of the last element is always equal to the size of the array
159) Then, the individual array element can be extracted with a WHERE filter
160) Type alias for user-defined type in select statement
161) UPDATE on varray column
162) UPDATE on varray column in PLSQL block
163) Update the first record in the nested table
164) Use for all loop to loop through the PLSQL table
165) Use For loop to output data in a PLSQL table of cursor
166) Use LOOP to output all elements in a table collection
167) Use of PLSQL tables of types
168) Uses the COUNT method to display the number of rows contained in an index-by table
169) Using a Nested Table Type to Define a Column in a Table
170) Using a Varray Type to Define a Column in a Table
171) Using INDEX BY BINARY_INTEGER
172) Using PLSQL to Create Functions to Access Elements
173) Using the EXISTS method
174) Using VARRAYs
175) VARRAY in action
176) VARRAY of Cursor
177) VARRAY of varchar2